5 Easy SQL INNER JOIN Examples for Beginners

Looking for a clear explanation of joins in SQL? Check out these five SQL INNER JOIN examples!

In SQL, INNER JOINs can be a bit difficult for beginners to master. But once you start working with them, you’ll learn they’re very useful! Let’s discuss five examples of SQL INNER JOINs. But first, let’s do a quick review of why JOINs matter.

In relational databases, data is organized and stored within tables. Each table represents a specific type of information. But, oftentimes, you need to analyze data from different tables simultaneously. And this is where JOINs step in.

We encourage you to practice joining tables by following our interactive SQL JOINs course. It includes 93 exercises covering the following topics:

So, are you ready to look at some examples of INNER JOIN? Let’s get started!

SQL JOINs combine data from two or more tables based on matching column values. For instance, you can merge customers’ information with the orders they made or link each product to its suppliers.

SQL JOINs enable you to join data from different tables and extract meaningful information for your specific use case. Check out this SQL JOIN Cheat Sheet to get the overview of different types of JOINs.

An INNER JOIN in SQL combines rows from multiple tables by matching their common column values. When you apply an INNER JOIN to link customer data with their corresponding orders, you'll see a list of customers who have placed at least one order.

In addition to INNER JOIN, SQL also provides other types of joins: LEFT JOIN, RIGHT JOIN, and FULL JOIN. Those joins are called OUTER JOINs. Unlike an INNER JOIN, an OUTER JOIN operation can list rows from both tables, even if there is no match.

Let’s look at an example to better understand the nature of SQL INNER JOINs.

Here is the customers table that stores customer information:

And here is the orders table that stores all orders made by the customers:

The common column (shown in pink) on which the customers and orders tables are joined is the customer_id column.

Now, here’s the query:

This query joins the customers table (aliased AS c) and the orders table (aliased AS o). These table aliases provide a clear way to inform the database of the source table of each column (using the syntax table_alias.column_name).

These aliases are employed within the ON clause to establish the column for joining tables. We also  use them in the SELECT statement, where two columns are selected from the customers table (c.customer_id and c.email) and another two columns from the orders table (o.order_date and o.order_amount).

Here is the output of the query:

You might observe that the customer with the ID of 3 does not appear in the resulting table. This particular customer hasn’t placed any orders yet; thus, they don’t have any matching records in the orders table.

The INNER JOIN keyword is interchangeable with the JOIN keyword. In other words, when you use JOIN, the database interprets it as a shorthand notation for INNER JOIN.

To learn more about INNER JOINs, check out our article What Is an Inner Join in SQL?.

Let's explore some SQL INNER JOIN examples tailored for beginners. Before jumping into the examples, check out this article on how to practice SQL JOINs.

You’ve got two tables that store information about books and authors. Join these tables to see the list of books along with their authors.

Here is the books table:

The book_id column uniquely identifies each book. And the author_id column assigns an author to each book.

And here is the authors table:

The author_id column uniquely identifies each author. And the author_name column stores authors’ full names.

If you want to see book titles and their author names, you can join these tables based on their common column (the author_id column in both tables). Just select title from books and author_name from authors:

This is the resulting table:

Please note that each book is assigned one author and each author is assigned one book, as there are no duplicate values in the common author_id column. Examine the next example to see what happens if the common column has duplicate values.

You’ve got two tables that store products and product categories. Join these tables to see the list of products along with their categories.

Here is the products table:

The product_id column uniquely identifies each product. And the category_id column assigns a category to each product.

And here is the categories table:

The category_id column uniquely identifies each category. And the category_name column stores full category names.

If you want to see products and their categories, you can join these tables based on their common column, which is the category_id column. Here’s the query:

This is the resulting table:

The categories with IDs of 22 and 23 are assigned to two products each; their names appear twice in the output table.

You’ve got two tables that store information about doctors and patients. Join these tables to see the list of doctors and patients who share the same first name.

Here is the doctors table:

The doctor_id column uniquely identifies each doctor. The other two columns store doctors’ first and last names.

And here is the patients table:

The patient_id column uniquely identifies each patient. The other two columns store patients’ first and last names.

If you want to see the list of doctors and patients who share the same first name, you can join these tables based on the first_name column.

Please note that d.* defines all columns from the doctors table and p.* defines all columns from the patients table.

This is the resulting table:

Thanks to the INNER JOIN operation, you can list only the doctor and patient pairs where the first names are the same.

You’ve got two tables that store information about apartments available for rent and people looking for apartments that fit their price range. Join these tables to see which apartments can be rented by which person.

Here is the apartments table:

The apartment_id column uniquely identifies each apartment. And the rent column stores the monthly rent amount.

And here is the persons table:

The person_id column uniquely identifies each person who is looking for an apartment to rent. The email column stores their emails and the max_rent column stores the maximum amount of monthly rent they are able to pay for the apartment.

If you want to see which apartments can be rented by which person, you can join these tables based on the rent and max_rent columns:

Here the match must be made between the rent column of the apartments table and the max_rent column of the persons table, where rent < max_rent.

This is the resulting table:

By using the ON clause with the condition containing the